# -*- coding: utf-8 -*-
"""
@Time: 1/5/2019 7:30 PM
@Author: hejing
@Email: 2010jing@gmail.com
"""


import pymysql
import random
import time

# 批量插的次数
loop_count = 10000
# 每次批量查的数据量
batch_size = 10000
success_count = 0
fails_count = 0

# conn = pymysql.connect(host='localhost', user='root', password='', database='gwsl', charset='utf8')
conn = pymysql.connect(host='120.79.254.19', user='root', password='zsdx2016', database='gwsl', charset='utf8')

chars = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz'
digits = '0123456789'


def random_generate_datetime():
	a1 = (2018, 1, 1, 0, 0, 0, 0, 0, 0)  # 设置开始日期时间元组（1976-01-01 00：00：00）
	a2 = (2018, 12, 31, 23, 59, 59, 0, 0, 0)  # 设置结束日期时间元组（1990-12-31 23：59：59）

	start = time.mktime(a1)  # 生成开始时间戳
	end = time.mktime(a2)  # 生成结束时间戳

	t = random.randint(start, end)  # 在开始和结束时间戳中随机取出一个
	date_touple = time.localtime(t)  # 将时间戳生成时间元组
	datetime = time.strftime("%Y-%m-%d %H:%M:%S", date_touple)  # 将时间元组转成格式化字符串（1976-05-21）
	# print(datetime)
	return datetime


def random_generate_string(length):
	return "".join(random.sample(chars, length))


def random_generate_number(length):
	if length > len(digits):
		digit_list = random.sample(digits, len(digits))
		digit_list.append(random.choice(digits))
		return ''.join(digit_list)
	return ''.join(random.sample(digits, length))


def random_generate_data(num):
	c = [num]
	phone_num_seed = 13100000000
	current_time = int(time.time())

	def _random_generate_data():
		c[0] += 1
		print(c)
		return (
			c[0],
			random_generate_string(15),
			random.choice('01'),
			random.randint(1, 120),
			phone_num_seed + c[0],
			random_generate_string(20),
			random_generate_string(10),
			current_time,
			current_time
		)

	return _random_generate_data


def random_generate_search_controller_data(base_cid, max_cid, base_light_pole_id):
	def _random_generate_search_controller_data():
		# get LightPole_id -- Controller_id
		Controller_id = random.randint(base_cid, max_cid)

		LightPole_id = Controller_id - base_cid + base_light_pole_id

		# Read_time
		Read_time = random_generate_datetime()

		# Electric_quantity 1--43
		Electric_quantity = random.randint(1, 43)

		# Electric_voltage 220~ 245
		Electric_voltage = random.randint(220, 245)

		# Electric_current 0 ~ 0.189
		Electric_current = random.randint(0, 189) / 1000

		# Power 0 ~ 46
		Power = random.randint(0, 46)
		return (
			LightPole_id,
			Controller_id,
			Read_time,
			'frog',
			'T8-501',
			Electric_quantity,
			Electric_voltage,
			Electric_current,
			Power,
			'1',
			'0',
			'0',
			'1',
			'0',
			'100'
		)

	return _random_generate_search_controller_data


def execute_many(insert_sql, batch_data):
	global success_count, fails_count
	cursor = conn.cursor()
	try:
		cursor.executemany(insert_sql, batch_data)
	except Exception as e:
		conn.rollback()
		fails_count = fails_count + len(batch_data)
		print(e)
		raise
	else:
		conn.commit()
		success_count = success_count + len(batch_data)
		print(str(success_count) + " commit")
	finally:
		cursor.close()


def start(base_cid, max_cid, base_light_pole_id):
	try:
		# user表列的数量
		# column_count = 9
		# 插入的SQL
		# insert_sql = "replace into user(id, name, sex, age, phone, address, password, create_time, update_time) values (" + ",".join(["%s" for x in range(column_count)]) + ")"

		insert_sql = "INSERT INTO `search_controller` (`s_Controller_id`, `LightPole_id`, `Controller_id`, `Read_time`, `Concentrator_name`, `Concentrator_address`, `Electric_quantity`, `Electric_voltage`, `Electric_current`, `Power`, `Power_factor`, `OneSwitch_status`, `TwoSwitch_status`, `Fault_status`, `LightOn_duration`, `brightness`) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

		# batch_count = 0
		begin_time = time.time()
		batch_data = []
		for x in range(loop_count):
			# batch_count = x * batch_size
			gen_fun = random_generate_search_controller_data(base_cid, max_cid, base_light_pole_id)
			batch_data = [gen_fun() for x in range(batch_size)]

			# print(batch_data)
			print(len(batch_data))

			execute_many(insert_sql, batch_data)
		end_time = time.time()
		total_sec = end_time - begin_time
		qps = success_count / total_sec
		print("总共生成数据： " + str(success_count))

		print("总共耗时(s): " + str(total_sec))

		print("QPS: " + str(qps))

	except Exception as e:
		print(e)

		raise
	else:
		pass
	finally:
		pass

def get_t_controller_cid():
	cursor = conn.cursor()

	sql = "SELECT cid FROM gwsl.t_controller order by cid desc limit 1;"

	res = cursor.execute(sql)
	result = cursor.fetchone()
	conn.commit()
	print('get_t_controller_cid:' + str(result[0]))

	return result[0]

def get_t_light_pole_lid():
	cursor = conn.cursor()

	sql = "SELECT lid FROM gwsl.t_light_pole order by lid desc limit 1;"

	res = cursor.execute(sql)
	result = cursor.fetchone()
	conn.commit()
	print('get_t_light_pole_lid:' + str(result[0]))

	return result[0]

if __name__ == '__main__':
	# x = random_generate_string(5)
	# print(x)
	# x = random_generate_number(5)
	# print(x)
	# x = random_generate_data(5)
	# y = x()
	# print(y)
	base_cid = get_t_controller_cid() - 10000 + 1
	max_cid = 10000 + base_cid - 1
	base_light_pole_id = get_t_light_pole_lid() - 10000 + 1
	# get_t_controller_cid()
	# get_t_light_pole_lid()
	start(base_cid, max_cid, base_light_pole_id)
